﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using BanditGame2.Web.Classes;

namespace BanditGame2.Web
{
    [ServiceContract(Namespace = "")]
    [SilverlightFaultBehavior]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class WCFService
    {
        static Random rnd = new Random();

        // Add more operations here and mark them with [OperationContract]
        [OperationContract]
        public int getLoginMatch(string username, string password)
        {
            try
            {
                int flag = 0;
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();
                
                da.SelectCommand = new SqlCommand("SELECT hashedPassword FROM [user] WHERE login=@ParamUsername", _sqlConnection);
                SqlParameter paramUsername = new SqlParameter("@ParamUsername", SqlDbType.VarChar, 20);
                paramUsername.Value = username;
                da.SelectCommand.Parameters.Add(paramUsername);
                
                DataSet ds = new DataSet();
                da.Fill(ds);
                _sqlConnection.Close();

                if (ds.Tables[0].Rows.Count > 0)
                {
                    if (PasswordHash.ValidatePassword(password, ds.Tables[0].Rows[0]["hashedPassword"].ToString())) 
                        flag = 1;
                }
                return flag;
            }
            catch (Exception ex)
            {
                ex.ToString();
                return 10;
            }
        }


        [OperationContract]
        public string getUserFullName(string login)
        {
            try
            {
                string name = "";
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = new SqlCommand("SELECT jmeno + ' ' + prijmeni FROM [user] WHERE login=@ParamUsername",
                                                  _sqlConnection);
                SqlParameter paramUsername = new SqlParameter("@ParamUsername", SqlDbType.VarChar, 15);
                paramUsername.Value = login;
                da.SelectCommand.Parameters.Add(paramUsername);

                DataSet ds = new DataSet();
                da.Fill(ds);
                _sqlConnection.Close();

                name = ds.Tables[0].Rows[0].ItemArray[0].ToString();

                return name;

            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }

        [OperationContract]
        public User getUserDetails(string login)
        {
            User user = new User();
            try
            {
            
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = new SqlCommand("SELECT jmeno, prijmeni, email FROM [user] WHERE login=@ParamUsername",
                                                  _sqlConnection);
                SqlParameter paramUsername = new SqlParameter("@ParamUsername", SqlDbType.VarChar, 15);
                paramUsername.Value = login;
                da.SelectCommand.Parameters.Add(paramUsername);

                DataSet ds = new DataSet();
                da.Fill(ds);
                _sqlConnection.Close();
                user.Jmeno = ds.Tables[0].Rows[0]["jmeno"].ToString();
                user.Prijmeni = ds.Tables[0].Rows[0]["prijmeni"].ToString();
                user.Email = ds.Tables[0].Rows[0]["email"].ToString();
                return user;

            }
            catch (Exception ex)
            {
                ex.ToString();
                return user;
            }
        }

        [OperationContract]
        public List<Round> getLevelHeader(int lvlNumber)
        {
            try
            {
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = new SqlCommand("SELECT id, playerX, playerY, width, height, time FROM [round] WHERE id=@ParamLvlNum",
                                                  _sqlConnection);
                SqlParameter paramLvlNumber = new SqlParameter("@ParamLvlNum", SqlDbType.Int);
                paramLvlNumber.Value = lvlNumber;
                da.SelectCommand.Parameters.Add(paramLvlNumber);

                DataSet ds = new DataSet();
                da.Fill(ds);
                _sqlConnection.Close();

                Rounds vRounds = new Rounds();
                foreach (System.Data.DataTable t in ds.Tables)
                {
                    
                    foreach (System.Data.DataRow dr in t.Rows)
                    {
                        Round vRound = new Round();
                        int vtryInt;
                        int.TryParse(dr["id"].ToString(), out vtryInt);
                        vRound.Id = vtryInt;
                        int.TryParse(dr["playerX"].ToString(), out vtryInt);
                        vRound.PlayerX = vtryInt;
                        int.TryParse(dr["playerY"].ToString(), out vtryInt);
                        vRound.PlayerY = vtryInt;
                        int.TryParse(dr["width"].ToString(), out vtryInt);
                        vRound.Width = vtryInt;
                        int.TryParse(dr["height"].ToString(), out vtryInt);
                        vRound.Height = vtryInt;
                        int.TryParse(dr["time"].ToString(), out vtryInt);
                        vRound.Time = vtryInt;

                        vRounds.All.Add(vRound);
                    }
                }


                return vRounds.All.ToList();
                

            }
            catch (Exception ex)
            {
                ex.ToString();
                return null;
            }
        }

        [OperationContract]
        public List<Field> getLevelFields(int lvlNumber)
        {
            try
            {
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = new SqlCommand("SELECT roundId, x, y, type, difficulty, radiusGuard FROM [field] WHERE roundId=@ParamLvlNum",
                                                  _sqlConnection);
                SqlParameter paramLvlNumber = new SqlParameter("@ParamLvlNum", SqlDbType.Int);
                paramLvlNumber.Value = lvlNumber;
                da.SelectCommand.Parameters.Add(paramLvlNumber);


                DataSet ds = new DataSet();
                da.Fill(ds);
                _sqlConnection.Close();

                Fields vFields = new Fields();
                foreach (System.Data.DataTable t in ds.Tables)
                {

                    foreach (System.Data.DataRow dr in t.Rows)
                    {
                        Field vField = new Field();
                        int vtryInt;
                        int.TryParse(dr["roundId"].ToString(), out vtryInt);
                        vField.RoundId = vtryInt;
                        int.TryParse(dr["x"].ToString(), out vtryInt);
                        vField.X = vtryInt;
                        int.TryParse(dr["y"].ToString(), out vtryInt);
                        vField.Y = vtryInt;
                        int.TryParse(dr["type"].ToString(), out vtryInt);
                        vField.Type = vtryInt;
                        int.TryParse(dr["difficulty"].ToString(), out vtryInt);
                        vField.Difficulty = vtryInt;
                        int.TryParse(dr["radiusGuard"].ToString(), out vtryInt);
                        vField.RadiusGuard = vtryInt;

                        vFields.All.Add(vField);
                    }
                }

                return vFields.All.ToList();
            }
            catch (Exception ex)
            {
                ex.ToString();
                return null;
            }
        }

        [OperationContract]
        public bool insertNewUser(User user)
        {
            try
            {
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();
                
                da.InsertCommand = new SqlCommand("INSERT INTO [user] (jmeno, prijmeni, login, admin, email, hashedPassword) VALUES (@ParamJmeno, @ParamPrijmeni, @ParamLogin, @ParamAdmin, @ParamEmail, @ParamHashedPassword)");
                SqlParameter paramJmeno = new SqlParameter("@ParamJmeno", SqlDbType.VarChar, 20);
                paramJmeno.Value = user.Jmeno;
                da.InsertCommand.Parameters.Add(paramJmeno);
                SqlParameter paramPrijmeni = new SqlParameter("@ParamPrijmeni", SqlDbType.VarChar, 20);
                paramPrijmeni.Value = user.Prijmeni;
                da.InsertCommand.Parameters.Add(paramPrijmeni);
                SqlParameter paramLogin = new SqlParameter("@ParamLogin", SqlDbType.VarChar, 15);
                paramLogin.Value = user.Login;
                da.InsertCommand.Parameters.Add(paramLogin);
                SqlParameter paramAdmin = new SqlParameter("@ParamAdmin", SqlDbType.Bit);
                paramAdmin.Value = user.Admin;
                da.InsertCommand.Parameters.Add(paramAdmin);
                SqlParameter paramEmail = new SqlParameter("@ParamEmail", SqlDbType.VarChar, 50);
                paramEmail.Value = user.Email;
                da.InsertCommand.Parameters.Add(paramEmail);
                SqlParameter paramHashedPassword = new SqlParameter("@ParamHashedPassword", SqlDbType.VarChar, 200);
                paramHashedPassword.Value = PasswordHash.CreateHash(user.Password);
                da.InsertCommand.Parameters.Add(paramHashedPassword);

                da.InsertCommand.Connection = _sqlConnection;
                da.InsertCommand.ExecuteNonQuery();
                _sqlConnection.Close();

                return true;

            }
            catch (Exception ex)
            {
                ex.ToString();
                return false;
            }
        }

        [OperationContract]
        public bool insertScore(string login, int score, int time)
        {
            try
            {
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.InsertCommand = new SqlCommand("INSERT INTO score(userLogin, score, datum) VALUES (@ParamLogin, @ParamScore, @ParamDate)");
                SqlParameter paramLogin = new SqlParameter("@ParamLogin", SqlDbType.VarChar, 15);
                paramLogin.Value = login;
                da.InsertCommand.Parameters.Add(paramLogin);
                SqlParameter paramScore = new SqlParameter("@ParamScore", SqlDbType.Int);
                paramScore.Value = score;
                da.InsertCommand.Parameters.Add(paramScore);
                SqlParameter paramDate = new SqlParameter("@ParamDate", SqlDbType.SmallDateTime);
                paramDate.Value = DateTime.Now;
                da.InsertCommand.Parameters.Add(paramDate);

                da.InsertCommand.Connection = _sqlConnection;
                da.InsertCommand.ExecuteNonQuery();
                _sqlConnection.Close();

                return true;

            }
            catch (Exception ex)
            {
                ex.ToString();
                return false;
            }
        }

        [OperationContract]
        public bool checkExistingUser(string login)
        {
            try
            {
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = new SqlCommand("SELECT login FROM [user] WHERE login=@ParamUsername", _sqlConnection);
                SqlParameter paramUsername = new SqlParameter("@ParamUsername", SqlDbType.VarChar, 15);
                paramUsername.Value = login;
                da.SelectCommand.Parameters.Add(paramUsername);

                DataSet ds = new DataSet();
                da.Fill(ds);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    return false;
                }

                _sqlConnection.Close();

                return true;
            }
            catch (Exception ex)
            {
                ex.ToString();
                return false;
            }
        }

        [OperationContract]
        public bool checkNextLevel(int lvlNumber)
        {
            try
            {
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = new SqlCommand("SELECT id FROM [round] WHERE id=@ParamId", _sqlConnection);
                SqlParameter paramId = new SqlParameter("@ParamId", SqlDbType.Int);
                paramId.Value = lvlNumber;
                da.SelectCommand.Parameters.Add(paramId);

                DataSet ds = new DataSet();
                da.Fill(ds);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    return true;
                }

                _sqlConnection.Close();

                return false;
            }
            catch (Exception ex)
            {
                ex.ToString();
                return false;
            }
        }

        [OperationContract]
        public Question getQuestion(int difficulty, int[] questionQueue )
        {
            try
            {
                Question question = new Question();
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = new SqlCommand("SELECT id, lvl, text FROM [question] WHERE lvl=@ParamLvl", _sqlConnection);
                SqlParameter paramLvl = new SqlParameter("@ParamLvl", SqlDbType.Int);
                paramLvl.Value = difficulty;
                da.SelectCommand.Parameters.Add(paramLvl);

                DataSet ds = new DataSet();
                da.Fill(ds);
                _sqlConnection.Close();

                Questions vQuestions = new Questions();
                foreach (System.Data.DataTable t in ds.Tables)
                {
                    foreach (System.Data.DataRow dr in t.Rows)
                    {
                        Question vQuestion = new Question();
                        int vtryInt;
                        int.TryParse(dr["id"].ToString(), out vtryInt);
                        vQuestion.Id = vtryInt;
                        int.TryParse(dr["lvl"].ToString(), out vtryInt);
                        vQuestion.Lvl = vtryInt;
                        vQuestion.Text = dr["text"].ToString();
                        vQuestions.All.Add(vQuestion);
                    }
                }

                
                //volba nahodne otazky pokud je vice nez 10 otazek dane obtiznosti
                if ((vQuestions.All.Count() > 10)&&questionQueue!=null)
                {
                    do
                    {
                        int r = rnd.Next(vQuestions.All.Count);
                        question = vQuestions.All[r];    
                    }while (questionQueue.Contains(question.Id));

                    return question;
                }
                else
                {
                    int r = rnd.Next(vQuestions.All.Count);
                    return vQuestions.All[r];
                }
            }
            catch (Exception ex)
            {
                ex.ToString();
                return null;
            }
        }

        [OperationContract]
        public List<Answer> getAnswers(int qId)
        {
            try
            {
                Question question = new Question();
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = new SqlCommand("SELECT id, questionId, mark, text, correct FROM [answer] WHERE questionId=@ParamQId", _sqlConnection);
                SqlParameter paramQId = new SqlParameter("@ParamQId", SqlDbType.Int);
                paramQId.Value = qId;
                da.SelectCommand.Parameters.Add(paramQId);

                DataSet ds = new DataSet();
                da.Fill(ds);
                _sqlConnection.Close();

                Answers vAnswers = new Answers();
                foreach (System.Data.DataTable t in ds.Tables)
                {
                    foreach (System.Data.DataRow dr in t.Rows)
                    {
                        Answer vAnswer = new Answer();
                        int vtryInt;
                        int.TryParse(dr["id"].ToString(), out vtryInt);
                        vAnswer.Id = vtryInt;
                        int.TryParse(dr["questionId"].ToString(), out vtryInt);
                        vAnswer.QuestionId = vtryInt;
                        char vtryChar;
                        char.TryParse(dr["mark"].ToString(), out vtryChar);
                        vAnswer.Mark = vtryChar;
                        vAnswer.Text = dr["text"].ToString();
                        bool vtryBool;
                        bool.TryParse(dr["correct"].ToString(), out vtryBool);
                        vAnswer.Correct = vtryBool;
                        vAnswers.All.Add(vAnswer);
                    }
                }
                return vAnswers.All.ToList();
            }
            catch (Exception ex)
            {
                ex.ToString();
                return null;
            }
        }

        [OperationContract]
        public List<Score> getScoreAll()
        {
            try
            {
                Score score = new Score();
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = new SqlCommand("SELECT userLogin, score, datum FROM [Score] ORDER BY score DESC", _sqlConnection);
                
                DataSet ds = new DataSet();
                da.Fill(ds);
                _sqlConnection.Close();

                Scores vScores = new Scores();
                foreach (System.Data.DataTable t in ds.Tables)
                {
                    foreach (System.Data.DataRow dr in t.Rows)
                    {
                        Score vScore = new Score();
                        vScore.UserLogin = (dr["userLogin"].ToString());
                        int vtryInt;
                        int.TryParse(dr["score"].ToString(), out vtryInt);
                        vScore.ScorePoints = vtryInt;
                        DateTime vtryDate;
                        DateTime.TryParse(dr["datum"].ToString(), out vtryDate);
                        vScore.Date = vtryDate;
                        vScores.All.Add(vScore);
                    }
                }
                return vScores.All.ToList();
            }
            catch (Exception ex)
            {
                ex.ToString();
                return null;
            }
        }

        [OperationContract]
        public List<Score> getScoreDate(DateTime Od, DateTime Do)
        {
            try
            {
                Score score = new Score();
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = new SqlCommand("SELECT userLogin, score, datum FROM [Score] WHERE datum<@ParamDo AND datum>@ParamOd ORDER BY score DESC", _sqlConnection);
                SqlParameter paramOd = new SqlParameter("@ParamOd", SqlDbType.SmallDateTime);
                paramOd.Value = Od;
                da.SelectCommand.Parameters.Add(paramOd);
                SqlParameter paramDo = new SqlParameter("@ParamDo", SqlDbType.SmallDateTime);
                paramDo.Value = Do;
                da.SelectCommand.Parameters.Add(paramDo);

                DataSet ds = new DataSet();
                da.Fill(ds);
                _sqlConnection.Close();

                Scores vScores = new Scores();
                foreach (System.Data.DataTable t in ds.Tables)
                {
                    foreach (System.Data.DataRow dr in t.Rows)
                    {
                        Score vScore = new Score();
                        vScore.UserLogin = (dr["userLogin"].ToString());
                        int vtryInt;
                        int.TryParse(dr["score"].ToString(), out vtryInt);
                        vScore.ScorePoints = vtryInt;
                        DateTime vtryDate;
                        DateTime.TryParse(dr["datum"].ToString(), out vtryDate);
                        vScore.Date = vtryDate;
                        vScores.All.Add(vScore);
                    }
                }
                return vScores.All.ToList();
            }
            catch (Exception ex)
            {
                ex.ToString();
                return null;
            }
        }

        [OperationContract]
        public bool updateUser(User user)
        {
            try
            {
                SqlConnection _sqlConnection = new SqlConnection();
                _sqlConnection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
                _sqlConnection.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.UpdateCommand = new SqlCommand("UPDATE [user] SET jmeno=@ParamJmeno, prijmeni=@ParamPrijmeni, email=@ParamEmail, hashedPassword=@ParamHashedPassword WHERE login=@ParamLogin");
                SqlParameter paramJmeno = new SqlParameter("@ParamJmeno", SqlDbType.VarChar, 20);
                paramJmeno.Value = user.Jmeno;
                da.UpdateCommand.Parameters.Add(paramJmeno);
                SqlParameter paramPrijmeni = new SqlParameter("@ParamPrijmeni", SqlDbType.VarChar, 20);
                paramPrijmeni.Value = user.Prijmeni;
                da.UpdateCommand.Parameters.Add(paramPrijmeni);
                SqlParameter paramLogin = new SqlParameter("@ParamLogin", SqlDbType.VarChar, 15);
                paramLogin.Value = user.Login;
                da.UpdateCommand.Parameters.Add(paramLogin);
                SqlParameter paramEmail = new SqlParameter("@ParamEmail", SqlDbType.VarChar, 50);
                paramEmail.Value = user.Email;
                da.UpdateCommand.Parameters.Add(paramEmail);
                SqlParameter paramHashedPassword = new SqlParameter("@ParamHashedPassword", SqlDbType.VarChar, 200);
                paramHashedPassword.Value = PasswordHash.CreateHash(user.Password);
                da.UpdateCommand.Parameters.Add(paramHashedPassword);

                da.UpdateCommand.Connection = _sqlConnection;
                da.UpdateCommand.ExecuteNonQuery();
                _sqlConnection.Close();

                return true;

            }
            catch (Exception ex)
            {
                ex.ToString();
                return false;
            }
        }

    }
    
}
